using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace QuanLyKhachHang.DAO
{
	#region using
	using System.Data;
	using QuanLyKhachHang.DTO;
    using System.Data.SqlClient;
	#endregion
	public class KhachHangDAO : AbstractDAO
	{
		public KhachHangDAO(String strPath): base(strPath)
		{
		}

		~KhachHangDAO()
		{
			base.Dispose();
		}

		public DataSet SelectAll()
		{
			DataSet ds;
			try
			{
				ds = new DataSet();
				base.sql = "usp_KhachHang_SelectAll";
				base.FillDataSet(ref ds, "KhachHang");
				return ds;
			}
			catch(System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
			finally
			{
				base.CleanUpObject();
			}
		}

		public DataSet SelectByID(int makh)
		{
			DataSet ds;
			try
			{
				ds = new DataSet();
				base.sql = "usp_KhachHang_SelectByID";
				base.InitializeCommand();
				base.AddParameter("@MAKH", SqlDbType.Int, 100, makh);
				base.FillDataSet(ref ds, "KhachHang");
				return ds;
			}
			catch(System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
			finally
			{
				base.CleanUpObject();
			}
		}

		public bool Insert(KhachHangDTO obj)
		{
			try
			{
				base.sql = "usp_KhachHang_Insert";
				base.InitializeCommand();
				base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, obj.MADONVI);
				base.AddParameter("@TENKH", SqlDbType.NVarChar, 100, obj.TENKH);
				base.AddParameter("@DIACHI", SqlDbType.NVarChar, 100, obj.DIACHI);
				return ExecuteStoredProcedure();
			}
			catch
			{
				return false;
			}
		}

        public bool InsertAndConcerns(KhachHangDTO objkh, TrichThuong_ChietKhauDTO objttck)
        {
            bool kq;
            try
            {
                base.sql = "usp_KhachHang_InsertAndConcerns";
                base.InitializeCommand();
                base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, objkh.MADONVI);
                base.AddParameter("@TENKH", SqlDbType.NVarChar, 100, objkh.TENKH);
                base.AddParameter("@DIACHI", SqlDbType.NVarChar, 100, objkh.DIACHI);
                base.AddParameter("@THANG", SqlDbType.Int, 100, objttck.THANG);
                base.AddParameter("@NAM", SqlDbType.Int, 100, objttck.NAM);
                base.AddParameter("@TONGTRICHTHUONG", SqlDbType.BigInt, 100, objttck.TONGTRICHTHUONG);
                base.AddParameter("@TONGCHIETKHAU", SqlDbType.BigInt, 100, objttck.TONGCHIETKHAU);
                base.AddParameter("@CHITRICHTHUONG", SqlDbType.BigInt, 100, objttck.CHITRICHTHUONG);
                base.AddParameter("@CHICHIETKHAU", SqlDbType.BigInt, 100, objttck.CHICHIETKHAU);
                base.AddParameterWithDirection("@MAKH", SqlDbType.BigInt, 100);
                kq = ExecuteStoredProcedure();
                objkh.MAKH = Int32.Parse(base.GetParamValue("@MAKH").ToString());
                return kq;
            }
            catch
            {
                return false;
            }
        }

		public bool Delete(int makh)
		{
			try
			{
				base.sql = "usp_KhachHang_Delete";
				base.InitializeCommand();
				base.AddParameter("@MAKH", SqlDbType.Int, 100, makh);
				return ExecuteStoredProcedure();
			}
			catch
			{
				return false;
			}
		}

		public bool Update(KhachHangDTO obj)
		{
			try
			{
				base.sql = "usp_KhachHang_Update";
				base.InitializeCommand();
				base.AddParameter("@MAKH", SqlDbType.Int, 100, obj.MAKH);
				base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, obj.MADONVI);
				base.AddParameter("@TENKH", SqlDbType.NVarChar, 100, obj.TENKH);
				base.AddParameter("@DIACHI", SqlDbType.NVarChar, 100, obj.DIACHI);
				return ExecuteStoredProcedure();
			}
			catch
			{
				return false;
			}
		}

        public bool UpdateAndConcerns(KhachHangDTO obj, TrichThuong_ChietKhauDTO objttDto)
        {
            try
            {
                base.sql = "usp_KhachHang_UpdateAndConcerns";
                base.InitializeCommand();
                base.AddParameter("@MAKH", SqlDbType.Int, 100, obj.MAKH);
                base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, obj.MADONVI);
                base.AddParameter("@TENKH", SqlDbType.NVarChar, 100, obj.TENKH);
                base.AddParameter("@DIACHI", SqlDbType.NVarChar, 100, obj.DIACHI);
                base.AddParameter("@THANG", SqlDbType.Int, 100, objttDto.THANG);
                base.AddParameter("@NAM", SqlDbType.Int, 100, objttDto.NAM);
                base.AddParameter("@TONGTRICHTHUONG", SqlDbType.Int, 100, objttDto.TONGTRICHTHUONG);
                base.AddParameter("@TONGCHIETKHAU", SqlDbType.Int, 100, objttDto.TONGCHIETKHAU);
                base.AddParameter("@CHITRICHTHUONG", SqlDbType.Int, 100, objttDto.CHITRICHTHUONG);
                base.AddParameter("@CHICHIETKHAU", SqlDbType.Int, 100, objttDto.CHICHIETKHAU);
                return ExecuteStoredProcedure();
            }
            catch
            {
                return false;
            }
        }

		public DataSet SelectByMADONVI(string madonvi)
		{
			DataSet ds;
			try
			{
				ds = new DataSet();
				base.sql = "usp_KhachHang_SelectByMADONVI";
				base.InitializeCommand();
				base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, madonvi);
				base.FillDataSet(ref ds, "KhachHang");
				return ds;
			}
			catch(System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
			finally
			{
				base.CleanUpObject();
			}
		}

        public DataSet SelectMAKH(string madonvi, string tenkh, string diachi)
        {
            DataSet ds;
            try
            {
                ds = new DataSet();
                base.sql = "usp_KhachHang_SelectMAKH";
                base.InitializeCommand();
                base.AddParameter("@MADONVI", SqlDbType.NVarChar, 100, madonvi);
                base.AddParameter("@TENKH", SqlDbType.NVarChar, 100, tenkh);
                base.AddParameter("@DIACHI", SqlDbType.NVarChar, 100, diachi);
                base.FillDataSet(ref ds, "KhachHang");
                return ds;
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            finally
            {
                base.CleanUpObject();
            }
        }

        public DataSet SearchKhachHang(KhachHangDTO objDto)
        {
            DataSet ds;
            try
            {
                ds = new DataSet();
                SqlCommand cmd = BuildQuery(objDto, objConnection);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds, "KhachHang");
                return ds;
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public SqlCommand BuildQuery(KhachHangDTO objDto, SqlConnection cn)
        {
            SqlCommand cmd = new SqlCommand();
            var strTenKH = " 1=1 ";
            var strDonVi = " 1=1 ";

            if (objDto.TENKH != "")
            {
                strTenKH = " kh.TENKH = N'" + objDto.TENKH + "'";
            }
            if (objDto.MADONVI != "")
            {
                strDonVi = " kh.MADONVI = N'" + objDto.MADONVI + "'";
            }

            string strDKWhere = " Where kh.MADONVI = dv.MADONVI AND ";
            strDKWhere += strTenKH;
            strDKWhere += " and " + strDonVi;

            string strSQL = " Select kh.*, dv.* From KhachHang AS kh, DonVi As dv ";
            strSQL += strDKWhere;
            strSQL += " Order by TENKH ";

            cmd.Connection = cn;
            cmd.CommandText = strSQL;

            return cmd;
        }
	}
}